package com.feather.tenia.oracle;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.feather.common.data.ColumnCollection;
import com.feather.common.data.simple.SimpleColumn;
import com.feather.tenia.Tenia;
import com.feather.tenia.adapter.ProcedureMetaAdapter;
import com.feather.tenia.adapter.TeniaAdapter;
import com.feather.tenia.adapter.util.CloseUtil;

public class OracleProcedureMeta extends ProcedureMetaAdapter {
	private static final long	serialVersionUID	= 1L;
	private String				callName;

	@Override
	public String getPrintName() {
		return getCallName();
	}

	@Override
	public String getCallName() {
		if (this.callName == null) {
			String original = getOriginal();
			String catalog = getCatalog();
			if (original != null && original.length() > 0) {
				this.callName = original;
			} else if (catalog != null && catalog.length() > 0) {
				this.callName = catalog + "." + getName();
			} else {
				this.callName = getName();
			}
		}
		return this.callName;
	}

	@Override
	public void loadColumns(Tenia tenia) throws SQLException {
		ColumnCollection columns = getColumns();

		StringBuilder builder = new StringBuilder();
		builder.append("SELECT package_name AS procedure_cat, ");
		builder.append("       owner AS procedure_schem, ");
		builder.append("       object_name AS procedure_name, ");
		builder.append("       argument_name AS column_name, ");
		builder.append("       DECODE(position, 0, 5, ");
		builder.append("                        DECODE(in_out, 'IN', 1, ");
		builder.append("                                       'OUT', 4, ");
		builder.append("                                       'IN/OUT', 2, ");
		builder.append("                                       0)) AS column_type, ");
		builder.append("       data_type, ");
		builder.append("       DECODE (data_precision, NULL, data_length, ");
		builder.append("                               data_precision) AS precision, ");
		builder.append("       data_length AS length, ");
		builder.append("       data_scale AS scale, ");
		builder.append("       10 AS radix, ");
		builder.append("       1 AS nullable, ");
		builder.append("       NULL AS remarks, ");
		builder.append("       sequence, ");
		builder.append("       overload, ");
		builder.append("       default_value ");
		builder.append("  FROM all_arguments ");
		builder.append(" WHERE ");
		if (getSchema() != null) {
			builder.append("   owner=? AND ");
		}
		if (getCatalog() != null) {
			builder.append("   package_name=? AND ");
		} else {
			builder.append("   package_name IS NULL AND ");
		}
		builder.append("       object_name=? ");
		builder.append(" ORDER BY sequence");

		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = tenia.getConnector().getConnection();
			statement = connection.prepareStatement(builder.toString());
			int index = 1;
			if (getSchema() != null) {
				statement.setString(index++, getSchema().toUpperCase());
			}
			if (getCatalog() != null) {
				statement.setString(index++, getCatalog().toUpperCase());
			}
			statement.setString(index, getName().toUpperCase());

			rs = statement.executeQuery();
			while (rs.next()) {
				SimpleColumn column = new SimpleColumn();
				column.setCatalog(rs.getString("PROCEDURE_CAT"));
				column.setSchema(rs.getString("PROCEDURE_SCHEM"));

				column.setName(rs.getString("COLUMN_NAME"));
				column.setType(rs.getShort("COLUMN_TYPE"));
				column.setDataTypeName(rs.getString("DATA_TYPE"));
				((TeniaAdapter) tenia).setColumnDataType(column);
				column.setOrdinal(rs.getInt("SEQUENCE"));
				column.setDefault(rs.getString("default_value"));

				column.setPrecision(rs.getInt("PRECISION"));
				column.setLength(rs.getInt("LENGTH"));
				column.setScale(rs.getShort("SCALE"));
				column.setRadix(rs.getShort("RADIX"));
				column.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.procedureNullable ? true : false);
				// column.setRemarks(rs.getString("REMARKS"));

				columns.add(column);
			}
		} finally {
			CloseUtil.close(rs, statement, connection);
		}
	}
}
